<?php

namespace Application\Model;

use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Sql;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Expression;
use Application\Model\SejourTable;

class PaysTable
{
    protected $table ='Pays';
    protected $tableName ='Pays';
    
    protected $tableGateway;
    protected $sejourTable;

    public function __construct(TableGateway $tableGateway, SejourTable $sejourTable)
    {
    	$this->tableGateway = $tableGateway;
        $this->sejourTable = $sejourTable;
    }
    
    public function fetchAll()
    {
        $resultSet = $this->tableGateway->select(function(Select $select){
            $select->order('libellePays ASC');
        });
        
        return $resultSet;
    }

    public function fetchAllArray()
    {
        $resultSet = $this->fetchAll();
        $entities = array();
        
        foreach ($resultSet as $row) {
        	$obj = new Pays();
        	$obj->exchangeArray( (array) $row);
        	$entities[] = $obj;
        }
        
        return $entities;
    }

    /* retourne tous les pays utilisés dans des séjours */
    public function fetchAllUsed() {

        $resultSet = $this->tableGateway->select(function(Select $select) {

            $sub2   = new Select( 'Regions' );
            $sub2->columns( array( 'total' => new Expression('COUNT(*)') ) )
                // ->join('Sejour', 'Sejour.regionId = Regions.idRegions', array(), 'inner')
                ->where(array(
                    new \Zend\Db\Sql\Predicate\Expression('Regions.idPays = Pays.idPays')
                ));


            $select->quantifier('DISTINCT');
            $select->columns(
                array(
                    '*',
                    'nombreRegionsAssocie' => new \Zend\Db\Sql\Expression( '?', array( $sub2 ) ),
                )
            );
            $select->join('Sejour', 'Sejour.paysId = Pays.idPays', array(), 'inner');
            $select->order('libellePays ASC');

            // echo $select->getSqlString();die;
            
        });

        return $this->toArray ($resultSet);
    }


    public function fetchAllUsed2()
    {

        // $resultSet = $this->tableGateway->select(function(Select $select) use($subquery) {
        //     $select->quantifier('DISTINCT');            
        //     $select->columns(array('*', 'nombreRegionsAssocie' => $subquery));
        //     $select->join('Sejour', 'Sejour.paysId = Pays.idPays', array(), 'inner');
        //     $select->order('libellePays ASC');
        // });

        return $this->toArray ($resultSet);
    }

    public function filtrerParmisLesPaysUtilises( $filtre )
    {
        $resultSet = $this->tableGateway->select(function(Select $select) use($filtre) {
            $select->quantifier('DISTINCT');
            $select->join('Sejour', 'Sejour.paysId = Pays.idPays', array(), 'inner');
            $select->where->like('Pays.libellePays', "%".$filtre."%");
            $select->order('libellePays ASC');
        });
        
        return $this->toArray ($resultSet);
    }
    
    public function toArray ($resultSet)
    {
        $entities = array();
        
        foreach ($resultSet as $row) {
            $obj = new Pays();
            $obj->exchangeArray( (array) $row);
            $entities[] = $obj;
        }
        
        return $entities;
    }

    public function initFiltresPays ( $values )
    {
        $sql = new Sql($this->tableGateway->getAdapter());
        $select = $sql->select();
        
        $select->from($this->tableGateway->table)
                ->where->in("Pays.idPays", $values);

        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();

        return $this->toArray ($resultSet);
    }

    public function getPaysByLibelle( $libelle )
    {
        $rowset = $this->tableGateway->select(array('libellePays' => ucfirst( $libelle ) ));
        $row = $rowset->current();
        return $row;
    }
}